using MySqlConnector;
using System.Data;
using System.Security.Cryptography;
using System.Text;
using System.Text.Json.Serialization;

namespace FinDataAdmin.Services
{
    public class AdminInfo
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("username")]
        public required string Username { get; set; }
        
        [JsonPropertyName("lastLogin")]
        public DateTime? LastLogin { get; set; }
        
        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }
    }

    public class ForumReport
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("postId")]
        public int PostId { get; set; }
        
        [JsonPropertyName("userId")]
        public int UserId { get; set; }
        
        [JsonPropertyName("username")]
        public string? Username { get; set; }
        
        [JsonPropertyName("reason")]
        public string Reason { get; set; }
        
        [JsonPropertyName("detail")]
        public string? Detail { get; set; }
        
        [JsonPropertyName("status")]
        public string Status { get; set; }
        
        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }
        
        [JsonPropertyName("updatedAt")]
        public DateTime UpdatedAt { get; set; }
    }

    public class UserPunishment
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }

        [JsonPropertyName("userId")]
        public int UserId { get; set; }

        [JsonPropertyName("username")]
        public string? Username { get; set; }

        [JsonPropertyName("punishmentType")]
        public string PunishmentType { get; set; }

        [JsonPropertyName("startDate")]
        public DateTime StartDate { get; set; }

        [JsonPropertyName("endDate")]
        public DateTime EndDate { get; set; }

        [JsonPropertyName("reason")]
        public string? Reason { get; set; }

        [JsonPropertyName("relatedReportId")]
        public int? RelatedReportId { get; set; }

        [JsonPropertyName("adminId")]
        public int AdminId { get; set; }

        [JsonPropertyName("adminName")]
        public string? AdminName { get; set; }

        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }

        [JsonPropertyName("isActive")]
        public bool IsActive { get; set; }

        [JsonPropertyName("durationDays")]
        public int DurationDays => (int)(EndDate - StartDate).TotalDays;
    }

    public class UserInfo
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("username")]
        public string Username { get; set; }
        
        [JsonPropertyName("lastLogin")]
        public DateTime? LastLogin { get; set; }
        
        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }
        
        [JsonPropertyName("status")]
        public string Status { get; set; } = "正常";
    }

    public class ForumPost
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("title")]
        public string Title { get; set; }
        
        [JsonPropertyName("userId")]
        public int? UserId { get; set; }
        
        [JsonPropertyName("username")]
        public string Username { get; set; }
        
        [JsonPropertyName("content")]
        public string Content { get; set; }
        
        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }
        
        [JsonPropertyName("updatedAt")]
        public DateTime UpdatedAt { get; set; }
        
        [JsonPropertyName("status")]
        public string Status { get; set; }
        
        [JsonPropertyName("commentCount")]
        public int CommentCount { get; set; }
        
        [JsonPropertyName("likeCount")]
        public int LikeCount { get; set; }
        
        [JsonPropertyName("viewCount")]
        public int ViewCount { get; set; }
        
        [JsonPropertyName("tags")]
        public List<string> Tags { get; set; } = new List<string>();
        
        [JsonPropertyName("images")]
        public List<ForumImage> Images { get; set; } = new List<ForumImage>();
        
        [JsonPropertyName("comments")]
        public List<ForumComment> Comments { get; set; } = new List<ForumComment>();
    }

    public class ForumImage
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("postId")]
        public int PostId { get; set; }
        
        [JsonPropertyName("imagePath")]
        public string ImagePath { get; set; }
        
        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }
    }

    public class ForumComment
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("postId")]
        public int PostId { get; set; }
        
        [JsonPropertyName("userId")]
        public int UserId { get; set; }
        
        [JsonPropertyName("content")]
        public string Content { get; set; }
        
        [JsonPropertyName("createdAt")]
        public DateTime CreatedAt { get; set; }
    }

    public class BaiduPanLink
    {
        [JsonPropertyName("id")]
        public int Id { get; set; }
        
        [JsonPropertyName("type")]
        public string Type { get; set; } // 'stock', 'index', 'financial'
        
        [JsonPropertyName("fileName")]
        public string FileName { get; set; }
        
        [JsonPropertyName("link")]
        public string Link { get; set; }
        
        [JsonPropertyName("code")]
        public string Code { get; set; }
        
        [JsonPropertyName("updatedAt")]
        public DateTime UpdatedAt { get; set; }
        
        [JsonPropertyName("updatedBy")]
        public int UpdatedBy { get; set; }
        
        [JsonPropertyName("adminName")]
        public string AdminName { get; set; }
    }

    public class DatabaseService
    {
        private readonly string _connectionString;
        private readonly int _maxRetryCount = 3;
        private readonly int _retryDelay = 2000; // 毫秒

        private readonly string _createAdminsTableSql = @"
            CREATE TABLE IF NOT EXISTS admins (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE,
                password_hash VARCHAR(255) NOT NULL,
                password_salt VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                last_login TIMESTAMP NULL
            );";

        private readonly string _createUserPunishmentsTableSql = @"
            CREATE TABLE IF NOT EXISTS user_punishments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                punishment_type VARCHAR(50) NOT NULL,
                start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                end_date TIMESTAMP NOT NULL,
                reason TEXT,
                related_report_id INT,
                created_by INT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE CASCADE
            );";

        private readonly string _createForumReportsTableSql = @"
            CREATE TABLE IF NOT EXISTS forum_reports (
                id INT AUTO_INCREMENT PRIMARY KEY,
                post_id INT NOT NULL,
                user_id INT NOT NULL,
                reason VARCHAR(100) NOT NULL,
                detail TEXT,
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            );";

        private readonly string _createBaiduPanLinksTableSql = @"
            CREATE TABLE IF NOT EXISTS baidu_pan_links (
                id INT AUTO_INCREMENT PRIMARY KEY,
                type VARCHAR(20) NOT NULL,
                file_name VARCHAR(100) NOT NULL,
                link TEXT NOT NULL,
                code VARCHAR(10) NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_by INT NOT NULL,
                INDEX (type),
                INDEX (updated_by)
            );";

        private readonly string _createForumImagesTableSql = @"
            CREATE TABLE IF NOT EXISTS forum_post_images (
                id INT AUTO_INCREMENT PRIMARY KEY,
                post_id INT NOT NULL,
                image_path TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX (post_id)
            );";

        private readonly string _createForumCommentsTableSql = @"
            CREATE TABLE IF NOT EXISTS forum_comments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                post_id INT NOT NULL,
                user_id INT NOT NULL,
                content TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX (post_id)
            );";

        public DatabaseService()
        {
            // 配置数据库连接字符串
            _connectionString = "Server=localhost;Port=3306;Database=findata;User=root;Password=root;";
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns>MySqlConnection对象</returns>
        public MySqlConnection GetConnection()
        {
            return new MySqlConnection(_connectionString);
        }

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <returns>连接成功返回true，否则返回false</returns>
        public async Task<bool> TestConnectionAsync()
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    Console.WriteLine("数据库连接测试成功");
                    return true;
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"数据库连接测试失败: {ex.Message}");
                Console.WriteLine($"错误代码: {ex.Number}");
                return false;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"数据库连接测试失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 初始化数据库，创建必要的表
        /// </summary>
        public async Task InitializeDatabaseAsync()
        {
            int retryCount = 0;
            while (true)
            {
                try
                {
                    using (var connection = new MySqlConnection(_connectionString))
                    {
                        await connection.OpenAsync();
                        
                        Console.WriteLine("正在检查数据库表结构...");
                        
                        // 检查表是否存在
                        bool adminsTableExists = await CheckTableExistsAsync(connection, "admins");
                        bool userPunishmentsTableExists = await CheckTableExistsAsync(connection, "user_punishments");
                        bool forumReportsTableExists = await CheckTableExistsAsync(connection, "forum_reports");
                        bool baiduPanLinksTableExists = await CheckTableExistsAsync(connection, "baidu_pan_links");
                        bool forumImagesTableExists = await CheckTableExistsAsync(connection, "forum_post_images");
                        bool forumCommentsTableExists = await CheckTableExistsAsync(connection, "forum_comments");
                        
                        // 创建管理员表（如果不存在）
                        if (!adminsTableExists)
                        {
                            Console.WriteLine("创建管理员表...");
                            using (var command = new MySqlCommand(_createAdminsTableSql, connection))
                            {
                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        
                        // 创建用户惩罚表（如果不存在）
                        if (!userPunishmentsTableExists)
                        {
                            Console.WriteLine("创建用户惩罚表...");
                            using (var command = new MySqlCommand(_createUserPunishmentsTableSql, connection))
                            {
                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        
                        // 创建论坛举报表（如果不存在）
                        if (!forumReportsTableExists)
                        {
                            Console.WriteLine("创建论坛举报表...");
                            using (var command = new MySqlCommand(_createForumReportsTableSql, connection))
                            {
                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        
                        // 创建百度网盘链接表（如果不存在）
                        if (!baiduPanLinksTableExists)
                        {
                            Console.WriteLine("创建百度网盘链接表...");
                            using (var command = new MySqlCommand(_createBaiduPanLinksTableSql, connection))
                            {
                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        
                        // 创建帖子图片表（如果不存在）
                        if (!forumImagesTableExists)
                        {
                            Console.WriteLine("创建帖子图片表...");
                            using (var command = new MySqlCommand(_createForumImagesTableSql, connection))
                            {
                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        
                        // 创建帖子评论表（如果不存在）
                        if (!forumCommentsTableExists)
                        {
                            Console.WriteLine("创建帖子评论表...");
                            using (var command = new MySqlCommand(_createForumCommentsTableSql, connection))
                            {
                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        
                        Console.WriteLine("数据库初始化成功");
                        break;
                    }
                }
                catch (MySqlException ex)
                {
                    retryCount++;
                    if (retryCount > _maxRetryCount)
                    {
                        Console.WriteLine($"数据库初始化失败，已达到最大重试次数: {ex.Message}");
                        throw;
                    }
                    
                    Console.WriteLine($"数据库初始化失败，将在{_retryDelay/1000}秒后重试: {ex.Message}");
                    await Task.Delay(_retryDelay);
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"数据库初始化失败: {ex.Message}");
                    throw;
                }
            }
        }
        
        /// <summary>
        /// 检查表是否存在
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="tableName">表名</param>
        /// <returns>表存在返回true，否则返回false</returns>
        private async Task<bool> CheckTableExistsAsync(MySqlConnection connection, string tableName)
        {
            string sql = @"
                SELECT COUNT(*) 
                FROM information_schema.tables 
                WHERE table_schema = DATABASE() 
                AND table_name = @TableName";
            
            using (var command = new MySqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@TableName", tableName);
                int count = Convert.ToInt32(await command.ExecuteScalarAsync());
                return count > 0;
            }
        }

        /// <summary>
        /// 验证管理员登录
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>成功返回管理员ID，失败返回0</returns>
        public async Task<int> ValidateAdminAsync(string username, string password)
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    // 查询管理员信息
                    string sql = "SELECT id, password_hash, password_salt FROM admins WHERE username = @Username";
                    using (var command = new MySqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                int adminId = reader.GetInt32(0);
                                string storedHash = reader.GetString(1);
                                string storedSalt = reader.GetString(2);
                                
                                // 验证密码
                                if (VerifyPassword(password, storedHash, storedSalt))
                                {
                                    // 更新最后登录时间
                                    await UpdateLastLoginAsync(adminId);
                                    return adminId;
                                }
                            }
                        }
                    }
                    
                    return 0; // 用户不存在或密码错误
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"验证管理员失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 创建管理员
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>成功返回true，失败返回false</returns>
        public async Task<bool> CreateAdminAsync(string username, string password)
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    // 检查用户名是否已存在
                    string checkSql = "SELECT COUNT(*) FROM admins WHERE username = @Username";
                    using (var checkCommand = new MySqlCommand(checkSql, connection))
                    {
                        checkCommand.Parameters.AddWithValue("@Username", username);
                        long count = (long)await checkCommand.ExecuteScalarAsync();
                        if (count > 0)
                        {
                            return false; // 用户名已存在
                        }
                    }
                    
                    // 创建密码哈希和盐
                    var (hash, salt) = CreatePasswordHash(password);
                    
                    // 插入管理员记录
                    string sql = @"
                        INSERT INTO admins (username, password_hash, password_salt)
                        VALUES (@Username, @PasswordHash, @PasswordSalt)";
                    
                    using (var command = new MySqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@PasswordHash", hash);
                        command.Parameters.AddWithValue("@PasswordSalt", salt);
                        
                        int rowsAffected = await command.ExecuteNonQueryAsync();
                        return rowsAffected > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"创建管理员失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 删除管理员
        /// </summary>
        /// <param name="adminId">管理员ID</param>
        /// <returns>成功返回true，失败返回false</returns>
        public async Task<bool> DeleteAdminAsync(int adminId)
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    string sql = "DELETE FROM admins WHERE id = @AdminId";
                    using (var command = new MySqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@AdminId", adminId);
                        
                        int rowsAffected = await command.ExecuteNonQueryAsync();
                        return rowsAffected > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"删除管理员失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 获取所有管理员
        /// </summary>
        /// <returns>管理员列表</returns>
        public async Task<List<AdminInfo>> GetAllAdminsAsync()
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    string sql = "SELECT id, username, created_at, last_login FROM admins ORDER BY id";
                    using (var command = new MySqlCommand(sql, connection))
                    {
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            var admins = new List<AdminInfo>();
                            while (await reader.ReadAsync())
                            {
                                admins.Add(new AdminInfo
                                {
                                    Id = reader.GetInt32(0),
                                    Username = reader.GetString(1),
                                    CreatedAt = reader.GetDateTime(2),
                                    LastLogin = reader.IsDBNull(3) ? null : reader.GetDateTime(3)
                                });
                            }
                            return admins;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取管理员列表失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 获取举报列表
        /// </summary>
        /// <param name="status">状态筛选，可选值：all, pending, resolved, rejected</param>
        /// <param name="page">页码，从1开始</param>
        /// <param name="pageSize">每页数量</param>
        /// <returns>举报列表</returns>
        public async Task<List<ForumReport>> GetReportsAsync(string status = "all", int page = 1, int pageSize = 20)
        {
            var reports = new List<ForumReport>();
            int offset = (page - 1) * pageSize;

            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    string statusFilter = status != "all" ? "WHERE r.status = @status" : "";
                    
                    string query = $@"
                        SELECT 
                            r.id, r.post_id, r.user_id, u.username, r.reason, 
                            r.detail, r.status, r.created_at, r.updated_at
                        FROM 
                            forum_reports r
                        LEFT JOIN 
                            users u ON r.user_id = u.id
                        {statusFilter}
                        ORDER BY 
                            r.created_at DESC
                        LIMIT @pageSize OFFSET @offset";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        if (status != "all")
                            cmd.Parameters.AddWithValue("@status", status);
                        
                        cmd.Parameters.AddWithValue("@pageSize", pageSize);
                        cmd.Parameters.AddWithValue("@offset", offset);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                reports.Add(new ForumReport
                                {
                                    Id = reader.GetInt32("id"),
                                    PostId = reader.GetInt32("post_id"),
                                    UserId = reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? null : reader.GetString("username"),
                                    Reason = reader.GetString("reason"),
                                    Detail = reader.IsDBNull("detail") ? null : reader.GetString("detail"),
                                    Status = reader.GetString("status"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at")
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取举报列表失败: {ex.Message}");
                throw;
            }

            return reports;
        }

        /// <summary>
        /// 更新举报状态
        /// </summary>
        /// <param name="reportId">举报ID</param>
        /// <param name="status">新状态，可选值：pending, resolved, rejected</param>
        /// <returns>成功返回true，失败返回false</returns>
        public async Task<bool> UpdateReportStatusAsync(int reportId, string status)
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    string query = @"
                        UPDATE forum_reports 
                        SET status = @status
                        WHERE id = @reportId";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@reportId", reportId);
                        cmd.Parameters.AddWithValue("@status", status);

                        int rowsAffected = await cmd.ExecuteNonQueryAsync();
                        return rowsAffected > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"更新举报状态失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 创建用户惩罚记录（禁言或封号）
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="punishmentType">惩罚类型，可选值：mute(禁言), ban(封禁)</param>
        /// <param name="durationDays">持续天数</param>
        /// <param name="reason">原因</param>
        /// <param name="adminId">操作的管理员ID</param>
        /// <param name="relatedReportId">相关举报ID</param>
        /// <returns>创建的惩罚记录ID</returns>
        public async Task<int> CreateUserPunishmentAsync(int userId, string punishmentType, int durationDays, string reason, int adminId, int? relatedReportId = null)
        {
            try
            {
                Console.WriteLine($"开始创建用户惩罚记录: userId={userId}, punishmentType={punishmentType}, durationDays={durationDays}, adminId={adminId}, relatedReportId={relatedReportId}");
                
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    // 计算结束时间
                    DateTime endDate = DateTime.Now.AddDays(durationDays);
                    Console.WriteLine($"惩罚结束时间: {endDate}");

                    // 插入惩罚记录
                    string query = @"
                        INSERT INTO user_punishments 
                        (user_id, punishment_type, end_date, reason, related_report_id, created_by)
                        VALUES 
                        (@userId, @punishmentType, @endDate, @reason, @relatedReportId, NULL);
                        SELECT LAST_INSERT_ID();";

                    Console.WriteLine("执行SQL: " + query);
                    
                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@userId", userId);
                        cmd.Parameters.AddWithValue("@punishmentType", punishmentType);
                        cmd.Parameters.AddWithValue("@endDate", endDate);
                        cmd.Parameters.AddWithValue("@reason", reason);
                        cmd.Parameters.AddWithValue("@relatedReportId", relatedReportId ?? (object)DBNull.Value);
                        // 不再使用adminId参数，设置为NULL

                        Console.WriteLine("执行数据库操作...");
                        var result = await cmd.ExecuteScalarAsync();
                        int newId = Convert.ToInt32(result);
                        Console.WriteLine($"创建成功，新记录ID: {newId}");
                        return newId;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"创建用户惩罚记录失败: {ex.Message}");
                Console.WriteLine($"异常详情: {ex}");
                throw;
            }
        }

        /// <summary>
        /// 获取用户惩罚历史
        /// </summary>
        /// <param name="userId">用户ID，如果为null则获取所有用户的惩罚记录</param>
        /// <param name="page">页码，从1开始</param>
        /// <param name="pageSize">每页数量</param>
        /// <returns>惩罚记录列表</returns>
        public async Task<List<UserPunishment>> GetUserPunishmentsAsync(int? userId = null, int page = 1, int pageSize = 20)
        {
            var punishments = new List<UserPunishment>();
            int offset = (page - 1) * pageSize;

            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    string userFilter = userId.HasValue ? "WHERE p.user_id = @userId" : "";
                    
                    string query = $@"
                        SELECT 
                            p.id, p.user_id, u.username, p.punishment_type, p.start_date, 
                            p.end_date, p.reason, p.related_report_id, 
                            p.created_by, a.username as admin_name, p.created_at
                        FROM 
                            user_punishments p
                        LEFT JOIN 
                            users u ON p.user_id = u.id
                        LEFT JOIN 
                            admins a ON p.created_by = a.id
                        {userFilter}
                        ORDER BY 
                            p.created_at DESC
                        LIMIT @pageSize OFFSET @offset";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        if (userId.HasValue)
                            cmd.Parameters.AddWithValue("@userId", userId.Value);
                        
                        cmd.Parameters.AddWithValue("@pageSize", pageSize);
                        cmd.Parameters.AddWithValue("@offset", offset);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                punishments.Add(new UserPunishment
                                {
                                    Id = reader.GetInt32("id"),
                                    UserId = reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? null : reader.GetString("username"),
                                    PunishmentType = reader.GetString("punishment_type"),
                                    StartDate = reader.GetDateTime("start_date"),
                                    EndDate = reader.GetDateTime("end_date"),
                                    Reason = reader.IsDBNull("reason") ? null : reader.GetString("reason"),
                                    RelatedReportId = reader.IsDBNull("related_report_id") ? (int?)null : reader.GetInt32("related_report_id"),
                                    AdminId = reader.IsDBNull("created_by") ? 0 : reader.GetInt32("created_by"),
                                    AdminName = reader.IsDBNull("admin_name") ? null : reader.GetString("admin_name"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    IsActive = reader.GetDateTime("end_date") > DateTime.Now
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取用户惩罚历史失败: {ex.Message}");
                throw;
            }

            return punishments;
        }

        /// <summary>
        /// 解除用户的所有处罚
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <returns>是否成功解除处罚</returns>
        public async Task<bool> RemoveUserPunishmentsAsync(int userId)
        {
            using var connection = GetConnection();
            await connection.OpenAsync();
            
            // 首先检查用户是否存在有效的处罚记录
            using (var checkCmd = new MySqlCommand(
                @"SELECT COUNT(*) FROM user_punishments 
                  WHERE user_id = @userId AND end_date > NOW()", connection))
            {
                checkCmd.Parameters.AddWithValue("@userId", userId);
                var count = Convert.ToInt32(await checkCmd.ExecuteScalarAsync());
                
                if (count == 0)
                {
                    Console.WriteLine($"用户ID={userId}没有有效的处罚记录");
                    return false;
                }
            }
            
            // 删除所有有效的处罚记录
            using (var deleteCmd = new MySqlCommand(
                @"DELETE FROM user_punishments 
                  WHERE user_id = @userId AND end_date > NOW()", connection))
            {
                deleteCmd.Parameters.AddWithValue("@userId", userId);
                int rowsAffected = await deleteCmd.ExecuteNonQueryAsync();
                
                Console.WriteLine($"用户ID={userId}的处罚解除结果：影响了{rowsAffected}行");
                return rowsAffected > 0;
            }
        }

        /// <summary>
        /// 获取系统统计信息
        /// </summary>
        /// <returns>统计信息字典</returns>
        public async Task<Dictionary<string, object>> GetSystemStatsAsync()
        {
            var stats = new Dictionary<string, object>();

            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    // 获取用户数量
                    using (var cmd = new MySqlCommand("SELECT COUNT(*) FROM users", connection))
                    {
                        var result = await cmd.ExecuteScalarAsync();
                        stats["userCount"] = Convert.ToInt32(result);
                    }

                    // 获取帖子数量
                    using (var cmd = new MySqlCommand("SELECT COUNT(*) FROM forum_posts", connection))
                    {
                        var result = await cmd.ExecuteScalarAsync();
                        stats["postCount"] = Convert.ToInt32(result);
                    }

                    // 获取待处理举报数量
                    using (var cmd = new MySqlCommand("SELECT COUNT(*) FROM forum_reports WHERE status = 'pending'", connection))
                    {
                        var result = await cmd.ExecuteScalarAsync();
                        stats["pendingReportCount"] = Convert.ToInt32(result);
                    }

                    // 获取活跃惩罚记录数量
                    using (var cmd = new MySqlCommand("SELECT COUNT(*) FROM user_punishments WHERE end_date > NOW()", connection))
                    {
                        var result = await cmd.ExecuteScalarAsync();
                        stats["activePunishmentCount"] = Convert.ToInt32(result);
                    }

                    // 获取评论数量
                    using (var cmd = new MySqlCommand("SELECT COUNT(*) FROM forum_comments", connection))
                    {
                        var result = await cmd.ExecuteScalarAsync();
                        stats["commentCount"] = Convert.ToInt32(result);
                    }
                    
                    // 获取点赞数量
                    using (var cmd = new MySqlCommand("SELECT COUNT(*) FROM forum_likes", connection))
                    {
                        var result = await cmd.ExecuteScalarAsync();
                        stats["likeCount"] = Convert.ToInt32(result);
                    }
                    
                    // 获取近7天的活动统计
                    stats["activityStats"] = await GetActivityStatsAsync(connection);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取系统统计信息失败: {ex.Message}");
                // 返回默认值
                stats["userCount"] = 0;
                stats["postCount"] = 0;
                stats["pendingReportCount"] = 0;
                stats["activePunishmentCount"] = 0;
                stats["commentCount"] = 0;
                stats["likeCount"] = 0;
                stats["activityStats"] = new Dictionary<string, object>
                {
                    ["dates"] = new List<string>(),
                    ["posts"] = new List<int>(),
                    ["comments"] = new List<int>(),
                    ["likes"] = new List<int>()
                };
            }

            return stats;
        }

        /// <summary>
        /// 获取近7天的活动统计数据
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <returns>活动统计数据</returns>
        private async Task<Dictionary<string, object>> GetActivityStatsAsync(MySqlConnection connection)
        {
            var result = new Dictionary<string, object>();
            var dates = new List<string>();
            var postsData = new List<int>();
            var commentsData = new List<int>();
            var likesData = new List<int>();

            try
            {
                // 获取近7天的日期
                for (int i = 6; i >= 0; i--)
                {
                    var date = DateTime.Now.AddDays(-i).ToString("yyyy-MM-dd");
                    dates.Add(date);
                }

                // 查询近7天每天的帖子数量
                string postsSql = @"
                    SELECT DATE(created_at) as date, COUNT(*) as count
                    FROM forum_posts
                    WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
                    GROUP BY DATE(created_at)
                    ORDER BY date";

                using (var cmd = new MySqlCommand(postsSql, connection))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        var postsByDate = new Dictionary<string, int>();
                        while (await reader.ReadAsync())
                        {
                            string date = reader.GetDateTime("date").ToString("yyyy-MM-dd");
                            int count = reader.GetInt32("count");
                            postsByDate[date] = count;
                        }

                        // 填充数据，确保每天都有值
                        foreach (var date in dates)
                        {
                            postsData.Add(postsByDate.ContainsKey(date) ? postsByDate[date] : 0);
                        }
                    }
                }

                // 查询近7天每天的评论数量
                string commentsSql = @"
                    SELECT DATE(created_at) as date, COUNT(*) as count
                    FROM forum_comments
                    WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
                    GROUP BY DATE(created_at)
                    ORDER BY date";

                using (var cmd = new MySqlCommand(commentsSql, connection))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        var commentsByDate = new Dictionary<string, int>();
                        while (await reader.ReadAsync())
                        {
                            string date = reader.GetDateTime("date").ToString("yyyy-MM-dd");
                            int count = reader.GetInt32("count");
                            commentsByDate[date] = count;
                        }

                        // 填充数据，确保每天都有值
                        foreach (var date in dates)
                        {
                            commentsData.Add(commentsByDate.ContainsKey(date) ? commentsByDate[date] : 0);
                        }
                    }
                }

                // 查询近7天每天的点赞数量
                string likesSql = @"
                    SELECT DATE(created_at) as date, COUNT(*) as count
                    FROM forum_likes
                    WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
                    GROUP BY DATE(created_at)
                    ORDER BY date";

                using (var cmd = new MySqlCommand(likesSql, connection))
                {
                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        var likesByDate = new Dictionary<string, int>();
                        while (await reader.ReadAsync())
                        {
                            string date = reader.GetDateTime("date").ToString("yyyy-MM-dd");
                            int count = reader.GetInt32("count");
                            likesByDate[date] = count;
                        }

                        // 填充数据，确保每天都有值
                        foreach (var date in dates)
                        {
                            likesData.Add(likesByDate.ContainsKey(date) ? likesByDate[date] : 0);
                        }
                    }
                }

                // 组装结果
                result["dates"] = dates;
                result["posts"] = postsData;
                result["comments"] = commentsData;
                result["likes"] = likesData;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取活动统计数据失败: {ex.Message}");
                
                // 返回空数据
                result["dates"] = dates;
                result["posts"] = new List<int>(new int[dates.Count]);
                result["comments"] = new List<int>(new int[dates.Count]);
                result["likes"] = new List<int>(new int[dates.Count]);
            }

            return result;
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <returns>用户信息对象，如果用户不存在则返回null</returns>
        public async Task<UserInfo?> GetUserInfoAsync(int userId)
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    string query = "SELECT id, username, last_login, created_at FROM users WHERE id = @UserId";
                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        cmd.Parameters.AddWithValue("@UserId", userId);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                return new UserInfo
                                {
                                    Id = reader.GetInt32(0),
                                    Username = reader.GetString(1),
                                    LastLogin = reader.IsDBNull(2) ? null : reader.GetDateTime(2),
                                    CreatedAt = reader.GetDateTime(3)
                                };
                            }
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取用户信息失败: {ex.Message}");
                return null;
            }
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="page">页码，从1开始</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="searchTerm">搜索关键词，可以是用户名或ID</param>
        /// <returns>用户列表和总数</returns>
        public async Task<(List<UserInfo> users, int totalCount)> GetUsersAsync(int page = 1, int pageSize = 20, string searchTerm = "")
        {
            var users = new List<UserInfo>();
            int totalCount = 0;
            int offset = (page - 1) * pageSize;

            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    // 构建查询条件
                    string whereClause = string.IsNullOrEmpty(searchTerm) ? "" : 
                        "WHERE username LIKE @SearchTerm OR id = @UserId";
                    
                    // 获取总数
                    string countSql = $"SELECT COUNT(*) FROM users {whereClause}";
                    using (var cmd = new MySqlCommand(countSql, connection))
                    {
                        if (!string.IsNullOrEmpty(searchTerm))
                        {
                            cmd.Parameters.AddWithValue("@SearchTerm", $"%{searchTerm}%");
                            int.TryParse(searchTerm, out int userId);
                            cmd.Parameters.AddWithValue("@UserId", userId);
                        }
                        
                        totalCount = Convert.ToInt32(await cmd.ExecuteScalarAsync());
                    }
                    
                    // 获取分页数据
                    string query = $@"
                        SELECT id, username, last_login, created_at
                        FROM users
                        {whereClause}
                        ORDER BY id DESC
                        LIMIT @PageSize OFFSET @Offset";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        if (!string.IsNullOrEmpty(searchTerm))
                        {
                            cmd.Parameters.AddWithValue("@SearchTerm", $"%{searchTerm}%");
                            int.TryParse(searchTerm, out int userId);
                            cmd.Parameters.AddWithValue("@UserId", userId);
                        }
                        cmd.Parameters.AddWithValue("@PageSize", pageSize);
                        cmd.Parameters.AddWithValue("@Offset", offset);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                users.Add(new UserInfo
                                {
                                    Id = reader.GetInt32("id"),
                                    Username = reader.GetString("username"),
                                    LastLogin = reader.IsDBNull("last_login") ? null : reader.GetDateTime("last_login"),
                                    CreatedAt = reader.GetDateTime("created_at")
                                });
                            }
                        }
                    }
                    
                    // 为每个用户获取当前的处罚状态
                    foreach (var user in users)
                    {
                        user.Status = await GetUserStatusAsync(connection, user.Id);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取用户列表失败: {ex.Message}");
            }

            return (users, totalCount);
        }
        
        /// <summary>
        /// 获取用户当前状态
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="userId">用户ID</param>
        /// <returns>用户状态</returns>
        private async Task<string> GetUserStatusAsync(MySqlConnection connection, int userId)
        {
            try
            {
                // 检查是否有活跃的禁言或封禁
                string query = @"
                    SELECT punishment_type 
                    FROM user_punishments 
                    WHERE user_id = @UserId AND end_date > NOW() 
                    ORDER BY end_date DESC 
                    LIMIT 1";
                
                using (var cmd = new MySqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@UserId", userId);
                    var result = await cmd.ExecuteScalarAsync();
                    
                    if (result != null)
                    {
                        string punishmentType = result.ToString();
                        return punishmentType == "ban" ? "已封禁" : "已禁言";
                    }
                    
                    return "正常";
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取用户状态失败: {ex.Message}");
                return "未知";
            }
        }

        /// <summary>
        /// 创建密码哈希
        /// </summary>
        /// <param name="password">密码</param>
        /// <returns>哈希和盐</returns>
        private (string hash, string salt) CreatePasswordHash(string password)
        {
            // 生成随机盐
            byte[] saltBytes = new byte[16];
            using (var rng = RandomNumberGenerator.Create())
            {
                rng.GetBytes(saltBytes);
            }
            string salt = Convert.ToBase64String(saltBytes);
            
            // 计算哈希
            string hash = ComputeHash(password, salt);
            
            return (hash, salt);
        }

        /// <summary>
        /// 计算密码哈希
        /// </summary>
        /// <param name="password">密码</param>
        /// <param name="salt">盐</param>
        /// <returns>哈希值</returns>
        private string ComputeHash(string password, string salt)
        {
            using (var sha256 = SHA256.Create())
            {
                // 将密码和盐组合
                string combined = password + salt;
                byte[] bytes = Encoding.UTF8.GetBytes(combined);
                
                // 计算哈希
                byte[] hashBytes = sha256.ComputeHash(bytes);
                
                // 转换为Base64字符串
                return Convert.ToBase64String(hashBytes);
            }
        }

        /// <summary>
        /// 验证密码
        /// </summary>
        /// <param name="password">输入的密码</param>
        /// <param name="storedHash">存储的哈希值</param>
        /// <param name="storedSalt">存储的盐</param>
        /// <returns>验证成功返回true，失败返回false</returns>
        private bool VerifyPassword(string password, string storedHash, string storedSalt)
        {
            // 使用相同的盐计算哈希
            string computedHash = ComputeHash(password, storedSalt);
            
            // 比较哈希值
            return computedHash == storedHash;
        }

        /// <summary>
        /// 更新最后登录时间
        /// </summary>
        /// <param name="adminId">管理员ID</param>
        private async Task UpdateLastLoginAsync(int adminId)
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    string sql = "UPDATE admins SET last_login = CURRENT_TIMESTAMP WHERE id = @AdminId";
                    using (var command = new MySqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@AdminId", adminId);
                        await command.ExecuteNonQueryAsync();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"更新最后登录时间失败: {ex.Message}");
                // 不抛出异常，因为这不是关键操作
            }
        }

        /// <summary>
        /// 获取帖子列表
        /// </summary>
        /// <param name="page">页码，从1开始</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="searchTerm">搜索关键词，可以是标题或用户名</param>
        /// <returns>帖子列表和总数</returns>
        public async Task<(List<ForumPost> posts, int totalCount)> GetPostsAsync(int page = 1, int pageSize = 20, string searchTerm = "")
        {
            var posts = new List<ForumPost>();
            int totalCount = 0;
            int offset = (page - 1) * pageSize;

            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    // 构建查询条件
                    string whereClause = string.IsNullOrEmpty(searchTerm) ? "" : 
                        "WHERE p.title LIKE @SearchTerm OR u.username LIKE @SearchTerm";
                    
                    // 获取总数
                    string countSql = $@"
                        SELECT COUNT(*) 
                        FROM forum_posts p
                        LEFT JOIN users u ON p.user_id = u.id
                        {whereClause}";
                    
                    using (var cmd = new MySqlCommand(countSql, connection))
                    {
                        if (!string.IsNullOrEmpty(searchTerm))
                        {
                            cmd.Parameters.AddWithValue("@SearchTerm", $"%{searchTerm}%");
                        }
                        
                        totalCount = Convert.ToInt32(await cmd.ExecuteScalarAsync());
                    }
                    
                    // 获取分页数据
                    string query = $@"
                        SELECT 
                            p.id, p.title, p.user_id, u.username, p.content, 
                            p.created_at, p.updated_at,
                            (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) as comment_count,
                            (SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) as like_count
                        FROM forum_posts p
                        LEFT JOIN users u ON p.user_id = u.id
                        {whereClause}
                        ORDER BY p.created_at DESC
                        LIMIT @PageSize OFFSET @Offset";

                    using (var cmd = new MySqlCommand(query, connection))
                    {
                        if (!string.IsNullOrEmpty(searchTerm))
                        {
                            cmd.Parameters.AddWithValue("@SearchTerm", $"%{searchTerm}%");
                        }
                        cmd.Parameters.AddWithValue("@PageSize", pageSize);
                        cmd.Parameters.AddWithValue("@Offset", offset);

                        using (var reader = await cmd.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                posts.Add(new ForumPost
                                {
                                    Id = reader.GetInt32("id"),
                                    Title = reader.GetString("title"),
                                    UserId = reader.IsDBNull("user_id") ? null : reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? null : reader.GetString("username"),
                                    Content = reader.IsDBNull("content") ? null : reader.GetString("content"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    CommentCount = reader.GetInt32("comment_count"),
                                    LikeCount = reader.GetInt32("like_count"),
                                    Status = "正常" // 默认状态，可以根据需要修改
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取帖子列表失败: {ex.Message}");
            }

            return (posts, totalCount);
        }
        
        /// <summary>
        /// 删除帖子
        /// </summary>
        /// <param name="postId">帖子ID</param>
        /// <returns>成功返回true，失败返回false</returns>
        public async Task<bool> DeletePostAsync(int postId)
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    // 删除帖子
                    string sql = "DELETE FROM forum_posts WHERE id = @PostId";
                    using (var command = new MySqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@PostId", postId);
                        
                        int rowsAffected = await command.ExecuteNonQueryAsync();
                        return rowsAffected > 0;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"删除帖子失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 获取帖子详情
        /// </summary>
        /// <param name="postId">帖子ID</param>
        /// <returns>帖子详情</returns>
        public async Task<ForumPost?> GetPostByIdAsync(int postId)
        {
            ForumPost? post = null;
            
            try
            {
                // 获取基本帖子信息
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    string query = @"
                        SELECT 
                            p.id, p.title, p.user_id, u.username, p.content, p.view_count,
                            p.created_at, p.updated_at,
                            (SELECT COUNT(*) FROM forum_comments WHERE post_id = p.id) as comment_count,
                            (SELECT COUNT(*) FROM forum_likes WHERE post_id = p.id) as like_count
                        FROM forum_posts p
                        LEFT JOIN users u ON p.user_id = u.id
                        WHERE p.id = @PostId";
                    
                    using (var command = new MySqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@PostId", postId);
                        
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                post = new ForumPost
                                {
                                    Id = reader.GetInt32("id"),
                                    Title = reader.GetString("title"),
                                    UserId = reader.IsDBNull("user_id") ? null : reader.GetInt32("user_id"),
                                    Username = reader.IsDBNull("username") ? "匿名用户" : reader.GetString("username"),
                                    Content = reader.IsDBNull("content") ? "" : reader.GetString("content"),
                                    ViewCount = reader.IsDBNull("view_count") ? 0 : reader.GetInt32("view_count"),
                                    CreatedAt = reader.GetDateTime("created_at"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    CommentCount = reader.GetInt32("comment_count"),
                                    LikeCount = reader.GetInt32("like_count"),
                                    Status = "正常"
                                };
                            }
                        }
                    }
                }
                
                // 如果找到了帖子，获取标签、图片和评论
                if (post != null)
                {
                    // 获取帖子标签
                    post.Tags = await GetPostTagsAsync(postId);
                    
                    // 获取帖子图片
                    post.Images = await GetPostImagesAsync(postId);
                    
                    // 获取帖子评论
                    post.Comments = await GetPostCommentsAsync(postId);
                }
                
                return post;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取帖子详情失败: {ex.Message}");
                return null;
            }
        }
        
        /// <summary>
        /// 获取帖子标签
        /// </summary>
        private async Task<List<string>> GetPostTagsAsync(int postId)
        {
            var tags = new List<string>();
            
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    string query = @"
                        SELECT t.name
                        FROM forum_post_tags pt
                        JOIN forum_tags t ON pt.tag_id = t.id
                        WHERE pt.post_id = @PostId";
                    
                    using (var command = new MySqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@PostId", postId);
                        
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                tags.Add(reader.GetString("name"));
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取帖子标签失败: {ex.Message}");
            }
            
            return tags;
        }
        
        /// <summary>
        /// 获取帖子图片
        /// </summary>
        private async Task<List<ForumImage>> GetPostImagesAsync(int postId)
        {
            var images = new List<ForumImage>();
            
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    string query = @"
                        SELECT id, post_id, image_path, created_at
                        FROM forum_post_images
                        WHERE post_id = @PostId";
                    
                    using (var command = new MySqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@PostId", postId);
                        
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                images.Add(new ForumImage
                                {
                                    Id = reader.GetInt32("id"),
                                    PostId = reader.GetInt32("post_id"),
                                    ImagePath = reader.GetString("image_path"),
                                    CreatedAt = reader.GetDateTime("created_at")
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取帖子图片失败: {ex.Message}");
            }
            
            return images;
        }

        /// <summary>
        /// 获取所有百度网盘链接
        /// </summary>
        /// <returns>百度网盘链接列表</returns>
        public async Task<List<BaiduPanLink>> GetAllBaiduPanLinksAsync()
        {
            var links = new List<BaiduPanLink>();
            
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    string query = @"
                        SELECT b.id, b.type, b.file_name, b.link, b.code, b.updated_at, b.updated_by, a.username as admin_name
                        FROM baidu_pan_links b
                        LEFT JOIN admins a ON b.updated_by = a.id
                        ORDER BY b.type ASC, b.updated_at DESC";
                    
                    using (var command = new MySqlCommand(query, connection))
                    {
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                links.Add(new BaiduPanLink
                                {
                                    Id = reader.GetInt32("id"),
                                    Type = reader.GetString("type"),
                                    FileName = reader.GetString("file_name"),
                                    Link = reader.GetString("link"),
                                    Code = reader.GetString("code"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    UpdatedBy = reader.GetInt32("updated_by"),
                                    AdminName = reader.IsDBNull("admin_name") ? "未知" : reader.GetString("admin_name")
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取百度网盘链接失败: {ex.Message}");
            }
            
            return links;
        }
        
        /// <summary>
        /// 获取特定类型的百度网盘链接
        /// </summary>
        /// <param name="type">链接类型</param>
        /// <returns>百度网盘链接信息</returns>
        public async Task<BaiduPanLink> GetBaiduPanLinkByTypeAsync(string type)
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    string query = @"
                        SELECT b.id, b.type, b.file_name, b.link, b.code, b.updated_at, b.updated_by, a.username as admin_name
                        FROM baidu_pan_links b
                        LEFT JOIN admins a ON b.updated_by = a.id
                        WHERE b.type = @Type
                        ORDER BY b.updated_at DESC
                        LIMIT 1";
                    
                    using (var command = new MySqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Type", type);
                        
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                return new BaiduPanLink
                                {
                                    Id = reader.GetInt32("id"),
                                    Type = reader.GetString("type"),
                                    FileName = reader.GetString("file_name"),
                                    Link = reader.GetString("link"),
                                    Code = reader.GetString("code"),
                                    UpdatedAt = reader.GetDateTime("updated_at"),
                                    UpdatedBy = reader.GetInt32("updated_by"),
                                    AdminName = reader.IsDBNull("admin_name") ? "未知" : reader.GetString("admin_name")
                                };
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取百度网盘链接失败: {ex.Message}");
            }
            
            return null;
        }
        
        /// <summary>
        /// 更新或创建百度网盘链接
        /// </summary>
        /// <param name="type">链接类型</param>
        /// <param name="fileName">文件名</param>
        /// <param name="link">链接地址</param>
        /// <param name="code">提取码</param>
        /// <param name="adminId">管理员ID</param>
        /// <returns>操作是否成功</returns>
        public async Task<bool> UpdateBaiduPanLinkAsync(string type, string fileName, string link, string code, int adminId)
        {
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    // 检查是否已存在该类型的链接
                    string checkQuery = "SELECT COUNT(*) FROM baidu_pan_links WHERE type = @Type";
                    bool exists = false;
                    
                    using (var command = new MySqlCommand(checkQuery, connection))
                    {
                        command.Parameters.AddWithValue("@Type", type);
                        var result = await command.ExecuteScalarAsync();
                        exists = Convert.ToInt32(result) > 0;
                    }
                    
                    string query;
                    if (exists)
                    {
                        // 更新现有记录
                        query = @"
                            UPDATE baidu_pan_links
                            SET file_name = @FileName, link = @Link, code = @Code, updated_at = NOW(), updated_by = @AdminId
                            WHERE type = @Type";
                    }
                    else
                    {
                        // 创建新记录
                        query = @"
                            INSERT INTO baidu_pan_links (type, file_name, link, code, updated_by)
                            VALUES (@Type, @FileName, @Link, @Code, @AdminId)";
                    }
                    
                    using (var command = new MySqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Type", type);
                        command.Parameters.AddWithValue("@FileName", fileName);
                        command.Parameters.AddWithValue("@Link", link);
                        command.Parameters.AddWithValue("@Code", code);
                        command.Parameters.AddWithValue("@AdminId", adminId);
                        
                        await command.ExecuteNonQueryAsync();
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"更新百度网盘链接失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 获取帖子评论
        /// </summary>
        /// <param name="postId">帖子ID</param>
        /// <returns>帖子评论列表</returns>
        public async Task<List<ForumComment>> GetPostCommentsAsync(int postId)
        {
            var comments = new List<ForumComment>();
            
            try
            {
                using (var connection = new MySqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    
                    string query = @"
                        SELECT c.id, c.post_id, c.user_id, c.content, c.created_at
                        FROM forum_comments c
                        WHERE c.post_id = @PostId
                        ORDER BY c.created_at DESC";
                    
                    using (var command = new MySqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@PostId", postId);
                        
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                comments.Add(new ForumComment
                                {
                                    Id = reader.GetInt32("id"),
                                    PostId = reader.GetInt32("post_id"),
                                    UserId = reader.GetInt32("user_id"),
                                    Content = reader.GetString("content"),
                                    CreatedAt = reader.GetDateTime("created_at")
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取帖子评论失败: {ex.Message}");
            }
            
            return comments;
        }
    }
} 